__author__ = 'Chunyan liu'

import openpyxl


def get_cell(workbook, str, sep):
    workbook = openpyxl.load_workbook('../../data/use_case/addClass_usecase.xlsx')
    str = '学生加班-数据:TC_001:class_no'
    sep = ':'
    list = str.split(sep)
    print(list)
    third_sheet = workbook[list[0]]


def read_excel(path):
    workbook = openpyxl.load_workbook(path)
    sheets = workbook.sheetnames
    main_sheet = workbook[sheets[0]]
    max_row = main_sheet.max_row
    max_col = main_sheet.max_column
    print('{} 最大行数：{},最大列数：{}'.format(sheets[0], max_row, max_col))
    for i in range(2, max_row+1):
        is_no = main_sheet.cell(row=i, column=5).value
        if is_no.upper() == 'N':
            value = main_sheet.cell(row=i, column=4).value
            next_sheet = workbook[value]
            max_row1 = next_sheet.max_row
            max_col1 = next_sheet.max_column
            print('{} 最大行数：{},最大列数：{}'.format(value, max_row1, max_col1))
            for ii in range(2, max_row1+1):
                row_text = []
                for jj in range(1, max_col1+1):
                    val1 = next_sheet.cell(row=ii, column=jj).value
                    row_text.append(val1)
                print(row_text)
                if max_col1 >= 7 and next_sheet.cell(row=ii, column=7).value:
                    data_str = next_sheet.cell(row=ii, column=7).value
                    print('第三张表：', data_str)
            print(' 第一张表的场景完毕，写回执行结果、执行结束时间')


if __name__ == '__main__':
    read_excel('../../data/use_case/addClass_usecase.xlsx')
    # get_cell('学生加班-数据:TC_001:class_no', ':')
